# --*--coding: utf-8 --*--
__project__ = 'TestHomeWork'
__fileName = 'test18'
__author = 'admin'
__time = '2021/4/27 11:38'
__product__ = 'PyCharm'

import pymysql

# 用字典封装数据库连接

dblist = {'host': '49.233.39.160',
          'database': 'lebo16',
          'port': 3306,
          'user': 'user',
          'password': 'leboAa!#$123',
          'charset': 'utf8'
          }


class StudegntInfo(object):

    # 初始化属性
    def __init__(self,
                 host=dblist["host"],
                 database=dblist["database"],
                 user=dblist["user"],
                 password=dblist["password"],
                 port=dblist["port"],
                 charset=dblist["charset"]
                 ):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset

    def get_connon(self):
        if not self.database:
            raise RuntimeError("配置信息有问题")
        try:
            self.conn = pymysql.connect(host=self.host,
                                        database=self.database,
                                        port=self.port,
                                        user=self.user,
                                        passwd=self.password,
                                        charset=self.charset)
        except Exception as rr:
            print('连接数据库失败%s' % rr)
        self.curor = self.conn.cursor(pymysql.cursors.DictCursor)
        return self.curor

    # 关闭数据库连接或游标
    def close_db_cursor(self):
        try:
            if self.curor:
                self.curor.close()
        except:
            raise RuntimeError('关闭游标异常')
        finally:
            try:
                if self.conn:
                    self.conn.close()
            except Exception as a:
                raise RuntimeError('关闭数据库异常%s' % a)
    #增删改
    def execu_sql(self, sql, parm=None):
        # 数据库连接、打开游标
        self.get_connon()
        # 执行sql
        count = self.curor.execute(sql,parm)
        # 提交事务
        self.conn.commit()
        if (count > 0):
            print('插入成功')
        else:
            print('插入失败')

    #查询所有数据
    def select_AllstuInfo(self,sql,parms=None):
        self.get_connon()
        count=self.curor.execute(sql,parms)
        if(count>0):
            list=  self.curor.fetchall()
            for i in list:
                print(i)
        else:
            print('没有数据')
        return count
    #查询单个数据
    def selet_stuInfoMany(self,sql,parms):
        self.get_connon()
        self.curor.execute(sql,parms)
        data=self.curor.fetchone()
        return data
if__name__ = '__main__'
# 实例化类对象
db = StudegntInfo()
#插入数据
name = str(input('请输入用户名：'))
hometown = str(input('请输入你的家乡地址：'))
className = str(input('请输入你的班级'))
Sql = 'insert into students17 (name,hometown,class) values(%s,%s,%s);'
print(Sql)
db.execu_sql(Sql,[name,hometown,className])
# 关闭游标和连接
db.close_db_cursor()

#查询所有数据
sql='select * from students17 '
db.select_AllstuInfo(sql)

#查询单个数据
name = str(input('请输入用户名：'))
sql='select * from students17 where name=%s'
data=db.selet_stuInfoMany(sql,[name])
print(data)